﻿#include "cdbopengaussoper.h"
#include "cdbtableinfomgr.h"
#include <QDebug>

//中文显示乱码问题
#define DB_A2T QString::fromLocal8Bit

CDbOpengaussOper::CDbOpengaussOper(const QString &sConnectInfo, const QString &sConnectName)
    : CDbBaseOper(sConnectInfo)
{
    QString str = sConnectName;
    if(str.isEmpty())
    {
        str = "Opengauss : " + sConnectInfo.left(5);
    }
    if (QSqlDatabase::contains(str))
    {
        m_db = QSqlDatabase::database(str);
    }
    else
    {
        m_db = QSqlDatabase::addDatabase("QPSQL", str);
    }
}

CDbOpengaussOper::CDbOpengaussOper(const CDataBaseConfig &cDbCfg)
    : CDbBaseOper(cDbCfg)
{
    QString str = cDbCfg.sConnectName;
    if(str.isEmpty())
    {
        str = "Opengauss : Oper";
    }
    if(QSqlDatabase::contains(str))
    {
        m_db = QSqlDatabase::database(str);
    }
    else
    {
        m_db = QSqlDatabase::addDatabase("QPSQL", str);
        //m_db = QSqlDatabase::addDatabase("QMYSQL", str);
    }
}

CDbOpengaussOper::~CDbOpengaussOper()
{
}

bool CDbOpengaussOper::isTableExist(CDbTableInfoMgr *pMgr)
{
    bool bRet = false;
    QString sSql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE ";
    sSql += "TABLE_SCHEMA = '" + pMgr->m_sMode + "' and ";
    sSql += "TABLE_NAME = '" + pMgr->m_sTableName + "' ";
    QSqlQuery query(m_db);
    if(false == query.exec()) return bRet;
    while(query.next())
    {
        QSqlRecord record = query.record();
        if(record.isEmpty()) continue;
        bRet = record.value(0).toInt() ? true : false;
    }
    return bRet;
}

QString CDbOpengaussOper::getTableName(CDbTableInfoMgr *pMgr)
{
    QString sTable = "\"" + pMgr->m_sMode + "\".";
    sTable += "\"" + pMgr->m_sTableName + "\"";
    return sTable;
}

QString CDbOpengaussOper::getFieldName(const QString &sSrc)
{
    QString sField = "\"";
    sField += analysisFieldName(sSrc);
    sField += "\"";
    QString sDest = sSrc;
    sDest = sDest.replace(analysisFieldName(sSrc), sField);
    return sDest;
}

QString CDbOpengaussOper::getFieldName(const QString &sSrc, CDbTableInfoMgr *pMgr)
{
    QString sDest = analysisFieldName(sSrc);
    foreach (QString str, pMgr->m_mapFieldValue.keys())
    {
        //都转为大写进行比较
        if(0 != str.toUpper().compare(sDest.toUpper())) continue;
        sDest = "\"";
        sDest += str;
        sDest += "\"";
        break;
    }
    QString sTemp = sSrc;
    sDest = sTemp.replace(analysisFieldName(sSrc), sDest);
    return sDest;
}

QString CDbOpengaussOper::analysisAppendSql(CDbTableInfoMgr *pMgr)
{
    //入参检查
    if(pMgr->m_sSql.isEmpty()) return "";

    QStringList listField;
    //先全部转为大写，方便关键字提取
    QString sTemp = pMgr->m_sSql.toUpper();
    int nOrederRet = sTemp.indexOf("ORDER BY ");
    int nLimitRet = sTemp.indexOf("LIMIT ");
    if(-1 == nOrederRet && -1 == nLimitRet)
    {
        writeLog(DB_A2T("未支持的解析格式，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelAlarm, def_FL);
        return pMgr->m_sSql;
    }
    //只处理排序，筛选不用处理
    if(-1 == nOrederRet) return pMgr->m_sSql;
    int nIndex = nOrederRet + 9;
    if(-1 != nLimitRet && nIndex >= nLimitRet)
    {
        writeLog(DB_A2T("未支持的解析格式，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelAlarm, def_FL);
        return pMgr->m_sSql;
    }
    //处理排序
    sTemp = pMgr->m_sSql.mid(nIndex, nLimitRet);
    //开始往后找字段
    QStringList listStr = sTemp.split(",");
    listStr.removeAll("");
    for(int i=0; i<listStr.size(); ++i)
    {
        QString sField = listStr.at(i);
        QStringList listOrder = sField.split(" ");
        listOrder.removeAll("");
        if(listOrder.isEmpty()) continue;
        sField = listOrder.at(0);
        listField.append(sField);
    }
    if(listField.isEmpty()) return "";
    QString sDest = pMgr->m_sSql;
    QStringList listDest;
    //找到原始字段和需要替换的字段
    foreach (QString key, pMgr->m_mapFieldValue.keys())
    {
        foreach (QString sField, listField)
        {
            int nRet = key.compare(sField, Qt::CaseInsensitive);
            if(0 != nRet) continue;
            listDest.append(key);
        }
    }
    //逐个替换
    for(int i=0; i<listField.size() && i<listDest.size(); ++i)
    {
        QString sField = listDest.at(i);
        QString sSrc = listField.at(i);
        QString sRet = "\"" + sField + "\"";
        sDest.replace(sSrc, sRet);
    }
    return sDest;
}

void CDbOpengaussOper::handleCustomSql(CDbTableInfoMgr *pMgr)
{
    if(pMgr->m_sSql.isEmpty()) return;
    //转为大写，方便识别
    QString sSql = pMgr->m_sSql.toUpper();
    QString sDest = pMgr->m_sSql;
    if(-1 != sSql.indexOf("INSERT "))
    {
        sDest = analysisInsertSql(pMgr);
    }
    else if(-1 != sSql.indexOf("DELETE "))
    {
        sDest = analysisDeleteSql(pMgr);
    }
    else if(-1 != sSql.indexOf("UPDATE "))
    {
        sDest = analysisUpdateSql(pMgr);
    }
    else if(-1 != sSql.indexOf("SELECT "))
    {
        sDest = analysisSelectSql(pMgr);
    }
    pMgr->m_sSql = sDest;
    handleExecuteSql(pMgr);
}

QString CDbOpengaussOper::analysisInsertSql(CDbTableInfoMgr *pMgr)
{
    QString sDest = pMgr->m_sSql;
    //转为大写，方便分割
    QString sUpperSql = pMgr->m_sSql.toUpper();
    //识别库表名，进行替换
    QString sTable = pMgr->m_sTableName.toUpper();
    int nRet = sUpperSql.indexOf(sTable);
    //未识别到库表名，返回原语句
    if(-1 == nRet)
    {
        writeLog(DB_A2T("解析插入语句失败，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelError, def_FL);
        return pMgr->m_sSql;
    }
    QString sTemp = pMgr->m_sSql.mid(nRet);
    int i = 0;
    for(; i<sTemp.size(); ++i)
    {
        if(' ' == sTemp[i] ) break;
    }
    QStringList listSrc;
    QStringList listDest;
    listSrc.append(sTemp.left(i));
    listDest.append(getTableName(pMgr));
    //解析字段
    int nLeftIndex = sUpperSql.indexOf("(");
    int nValuesIndex = sUpperSql.indexOf("VALUES");
    //有字段
    if(nLeftIndex < nValuesIndex)
    {
        int nRightIndex = sUpperSql.indexOf(")");
        QString sField = pMgr->m_sSql.mid(nLeftIndex+1, nRightIndex-nLeftIndex-1);
        QStringList listField = sField.split(",");
        foreach (QString str, listField)
        {
            //删除空格，提取字段
            sField = str.remove(" ");
            listSrc.append(sField);
            listDest.append(getFieldName(sField, pMgr));
        }
    }
    //进行统一替换
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    return sDest;
}

QString CDbOpengaussOper::analysisDeleteSql(CDbTableInfoMgr *pMgr)
{
    QString sDest = pMgr->m_sSql;
    //转为大写，方便分割
    QString sUpperSql = pMgr->m_sSql.toUpper();
    //识别库表名，进行替换
    QString sTable = pMgr->m_sTableName.toUpper();
    int nRet = sUpperSql.indexOf(sTable);
    //未识别到库表名，返回原语句
    if(-1 == nRet)
    {
        writeLog(DB_A2T("解析插入语句失败，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelError, def_FL);
        return pMgr->m_sSql;
    }
    QString sTemp = pMgr->m_sSql.mid(nRet);
    int i = 0;
    for(; i<sTemp.count(); ++i)
    {

        if(' ' == sTemp[i]) break;
    }
    QStringList listSrc;
    QStringList listDest;
    listSrc.append(sTemp.left(i));
    listDest.append(getTableName(pMgr));
    //进行统一替换
    for(int i=0; i<listSrc.count() && i<listDest.count(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField);
        sDest.replace(nIndex, sSrcField.count(), sDestField);
    }
    pMgr->m_sSql = sDest;
    //关键字位置索引
    handleWhere(pMgr);              //处理WHERE关键字
    return pMgr->m_sSql;
}

QString CDbOpengaussOper::analysisUpdateSql(CDbTableInfoMgr *pMgr)
{
    //转为大写，方便分割
    QString sUpperSql = pMgr->m_sSql.toUpper();
    //识别库表名，进行替换
    QString sTable = pMgr->m_sTableName.toUpper();
    int nRet = sUpperSql.indexOf(sTable);
    //未识别到库表名，返回原语句
    if(-1 == nRet)
    {
        writeLog(DB_A2T("解析更新语句失败，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelError, def_FL);
        return pMgr->m_sSql;
    }
    //识别库表名
    QString sTemp = pMgr->m_sSql.mid(nRet);
    int i = 0;
    for(; i<sTemp.size(); ++i)
    {
        if(' ' == sTemp[i]) break;
    }
    QStringList listSrc;
    QStringList listDest;
    listSrc.append(sTemp.left(i));
    listDest.append(getTableName(pMgr));
    //进行统一替换
    QString sDest = pMgr->m_sSql;
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    pMgr->m_sSql = sDest;
    //关键字位置索引
    handleSet(pMgr);                //处理SET关键字
    handleWhere(pMgr);              //处理WHERE关键字
    return pMgr->m_sSql;
}

QString CDbOpengaussOper::analysisSelectSql(CDbTableInfoMgr *pMgr)
{
    //转为大写，方便分割
    QString sUpperSql = pMgr->m_sSql.toUpper();
    //识别库表名，进行替换
    QString sTable = pMgr->m_sTableName.toUpper();
    int nRet = sUpperSql.indexOf(sTable);
    //未识别到库表名，返回原语句
    if(-1 == nRet)
    {
        writeLog(DB_A2T("解析查询语句失败，SQL:%1").arg(pMgr->m_sSql),
                 CLogBase::enLevelError, def_FL);
        return pMgr->m_sSql;
    }
    //识别库表名
    QString sTemp = pMgr->m_sSql.mid(nRet);
    int i = 0;
    for(; i<sTemp.size(); ++i)
    {
        if(' ' == sTemp[i]) break;
    }
    QStringList listSrc;
    QStringList listDest;
    listSrc.append(sTemp.left(i));
    listDest.append(getTableName(pMgr));
    //解析字段
    int nStarIndex = sUpperSql.indexOf("*");
    int nFromIndex = sUpperSql.indexOf("FROM");
    //查询字段
    if(-1 == nStarIndex || (-1 != nStarIndex && nStarIndex > nFromIndex))
    {
        int nLeftIndex = sUpperSql.indexOf("SELECT ");
        QString sField = pMgr->m_sSql.mid(nLeftIndex+7, nFromIndex-(nLeftIndex+7));
        QStringList listField = sField.split(",");
        foreach (QString str, listField)
        {
            //删除空格，提取字段
            sField = str.remove(" ");
            listSrc.append(sField);
            listDest.append(getFieldName(sField, pMgr));
        }
    }
    QString sDest = pMgr->m_sSql;
    //进行统一替换
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    pMgr->m_sSql = sDest;
    //关键字位置索引
    handleWhere(pMgr);              //处理WHERE关键字
    handleOrderby(pMgr);            //处理ORDER BY关键字
    return pMgr->m_sSql;
}

void CDbOpengaussOper::handleWhere(CDbTableInfoMgr *pMgr)
{
    QMap<int, QString> mapInfo;
    int nWhere = getKeyIndex(enKEYWHERE, pMgr, mapInfo);
    if(-1 == nWhere) return ;
    int nRightIndex = -1;
    foreach (int key, mapInfo.keys())
    {
        if(key <= nWhere) continue;
        nRightIndex = key;
        break;
    }
    //获得where后面的字符串
    QString sWhereSql = pMgr->m_sSql.mid(nWhere + 7, nRightIndex - (nWhere + 7));
    QStringList listWhere;
    //使用and进行分割
    listWhere = sWhereSql.split(" and ");
    listWhere.removeAll("");
    //使用or进行分割
    QStringList listSrc;
    foreach (QString str, listWhere)
    {
        listSrc.append(str.split(" or "));
    }
    listSrc.removeAll("");
    QStringList listDest;
    foreach (QString str, listSrc)
    {
        listDest.append(getFieldName(str, pMgr));
    }
    QString sDest = pMgr->m_sSql;
    //进行统一替换
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField, nWhere);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    pMgr->m_sSql = sDest;
}

void CDbOpengaussOper::handleOrderby(CDbTableInfoMgr *pMgr)
{
    QMap<int, QString> mapInfo;
    int nOrderBy = getKeyIndex(enKEYORDERBY, pMgr, mapInfo);
    if(-1 == nOrderBy) return;
    int nRightIndex = -1;
    foreach (int key, mapInfo.keys())
    {
        if(key <= nOrderBy) continue;
        nRightIndex = key;
        break;
    }
    //获得where后面的字符串
    QString sOrderBySql = pMgr->m_sSql.mid(nOrderBy + 10, nRightIndex - (nOrderBy + 10));
    //使用,进行分割
    QStringList listField;
    listField = sOrderBySql.split(",");
    listField.removeAll("");
    QStringList listSrc;
    foreach (QString str, listField)
    {
        QStringList listTemp = str.split(" ");
        listTemp.removeAll("");
        if(listTemp.isEmpty()) continue;
        listSrc.append(listTemp.at(0));
    }
    QStringList listDest;
    foreach (QString str, listSrc)
    {
        listDest.append(getFieldName(str, pMgr));
    }
    //进行统一替换
    QString sDest = pMgr->m_sSql;
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField, nOrderBy);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    pMgr->m_sSql = sDest;
}

void CDbOpengaussOper::handleSet(CDbTableInfoMgr *pMgr)
{
    QMap<int, QString> mapInfo;
    int nSet = getKeyIndex(enKEYSET, pMgr, mapInfo);
    if(-1 == nSet) return ;
    int nRightIndex = -1;
    foreach (int key, mapInfo.keys())
    {
        if(key <= nSet) continue;
        nRightIndex = key;
        break;
    }
    //获得set后面的字符串
    QString sSetSql = pMgr->m_sSql.mid(nSet + 5, nRightIndex - (nSet + 5));
    QStringList listSrc;
    //使用,进行分割
    listSrc = sSetSql.split(",");
    listSrc.removeAll("");
    QStringList listDest;
    foreach (QString str, listSrc)
    {
        listDest.append(getFieldName(str, pMgr));
    }
    QString sDest = pMgr->m_sSql;
    //进行统一替换
    for(int i=0; i<listSrc.size() && i<listDest.size(); ++i)
    {
        QString sSrcField = listSrc.at(i);
        QString sDestField = listDest.at(i);
        int nIndex = sDest.indexOf(sSrcField, nSet);
        sDest.replace(nIndex, sSrcField.size(), sDestField);
    }
    pMgr->m_sSql = sDest;
}

void CDbOpengaussOper::handleIn(CDbTableInfoMgr *pMgr)
{
    Q_UNUSED(pMgr)
#if 0
    QMap<int, QString> mapInfo;
    int nIN = getKeyIndex(enKEYIN, pMgr, mapInfo);
    if(-1 == nIN) return ;
    int nRightIndex = -1;
    foreach (int key, mapInfo.keys())
    {
        if(key <= nIN) continue;
        nRightIndex = key;
        break;
    }
    //获得in后面的字符串（由后面的左右圆括号来确定语句范围）暂未实现
#endif
}

int CDbOpengaussOper::getKeyIndex(const int &nType, CDbTableInfoMgr *pMgr, QMap<int, QString> &mapIndex)
{
    QString sUpperSql = pMgr->m_sSql.toUpper();
    QMap<int, QString> mapInfo;
    int nIN = sUpperSql.indexOf(" IN ");
    if(-1 != nIN) mapInfo.insert(nIN, " IN ");
    int nOrderBy = sUpperSql.indexOf(" ORDER BY ");
    if(-1 != nOrderBy) mapInfo.insert(nOrderBy, " ORDER BY ");
    int nWhere = sUpperSql.indexOf(" WHERE ");
    if(-1 != nWhere) mapInfo.insert(nWhere, " WHERE ");
    int nLimit = sUpperSql.indexOf(" LIMIT ");
    if(-1 != nLimit) mapInfo.insert(nLimit, " LIMIT ");
    int nSet = sUpperSql.indexOf(" SET ");
    if(-1 != nSet) mapInfo.insert(nSet, " SET ");
    int nRet = -1;
    switch (nType)
    {
    case enKEYSET:
        nRet = nSet;
        break;
    case enKEYWHERE:
        nRet = nWhere;
        break;
    case enKEYORDERBY:
        nRet = nOrderBy;
        break;
    case enKEYLIMIT:
        nRet = nLimit;
        break;
    case enKEYIN:
        nRet = nIN;
        break;
    default:
        break;
    }
    if(-1 != nRet) mapIndex = mapInfo;
    return nRet;
}
